﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;

namespace Dal
{
    public class DalMajor
    {
        SqlCommand cmd = null;
        string sql = "";
        public DataTable Nav_Name_Singlex()
        {
            //二级导航全部信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                string sql = "select * from SubNavigation left join Major on SubNavigation.Id=Major.NavSubId where BelongTo='3' and SubNavigation.Deleted='false' and _Content is null";

                cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
                SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
                DataSet ds = new System.Data.DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                return dt;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }
        public DataTable Retrieve_Information(ModelMajor m)
        {
            //检索
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                string sql = "select Id,NavId,NavSubId,Title,_Content,Author,CONVERT(varchar(10), DateTime,120) as DateTime,Number from Major where (Title like '%" + m.Title + "%' or DateTime like '%" + m.DateTime + "%' or Author like '%" + m.Author + "%') and NavId='3' and Deleted='false' order by DateTime desc";

                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                return dt;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;

        }
        public bool Data_Delete(ModelMajor m)
        {
            //专业删除
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            
            try
            {
                string sql = "update Major set Deleted='true' where Id='"+m.Id+"'";

                cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }
        public DataTable All_Information()
        {
            //全部信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {

                string sql = "select Id,NavId,NavSubId,Title,_Content,Author,CONVERT(VARCHAR(10),DateTime,120) as DateTime from Major where Deleted='false' order by DateTime desc";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
                DataSet ds = new System.Data.DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }
        public DataTable Singlex_Information(ModelMajor m)
        {
            //查询单个信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                sql = "select Id,Title,NavSubId,Title,_Content,CONVERT(VARCHAR(10),DateTime,120) as DateTime,Author,Number from Major where Id='" + m.Id + "'";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new System.Data.DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt.Rows.Count > 0)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }
        public bool Update_Information(ModelMajor m)
        {
            //修改
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                string sql = "";
                if (m.SqlName == "0")
                {
                    sql = "update Major set Title='" + m.Title + "',_Content='" + m.Word + "',Author='" + m.Author + "',DateTime='" + m.ReleaseTime + "',NavSubId='" + m.SecondId + "',Number='" + m.BrowseNumber + "' where Id='" + m.Id + "'";
                }
                if (m.SqlName == "1")
                {
                    if (!IsTitle(m))
                        sql = "insert into Major(NavId,NavSubId,Title,_Content,Author,DateTime,Number)values('3','" + m.SecondId + "','" + m.Title + "','" + m.Word + "','" + m.Author + "','" + m.DateTime + "','" + m.BrowseNumber + "')";
                    else
                        return false;
                }
                cmd = new System.Data.SqlClient.SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }
        private bool IsTitle(ModelMajor m)
        {
            //判断标题是否存在
            //IsProfessional
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                sql = "select count(*) from Major where Title='" + m.Title + "'";
                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteScalar().ToString() != "0")
                    return true;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }
    }
}
